//
//  JEHDataBaseHandle.m
//  SinaNews
//
//  Created by 蒋恩浩 on 15/12/10.
//  Copyright © 2015年 JEH. All rights reserved.
//

#import "JEHDataBaseHandle.h"

@interface JEHDataBaseHandle ()

@property (nonatomic, copy) NSString *dataBasePath;

@end

static JEHDataBaseHandle *dataBaseHandle;

@implementation JEHDataBaseHandle

- (void)dealloc {
    
    [_nameArray release];
    [_urlArray release];
    [_numberArray release];
    [_dataBasePath release];
    [super dealloc];
}

+ (instancetype)shareDataBaseHandle {
    if (!dataBaseHandle) {
        dataBaseHandle = [[JEHDataBaseHandle alloc] init];
    }
    return dataBaseHandle;
}

- (NSString *)dataBasePath {
    if (!_dataBasePath) {
        NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *path = [paths firstObject];
        self.dataBasePath = [NSString stringWithFormat:@"%@/JEHObject.sqlite", path];
    }
    return _dataBasePath;
}

static sqlite3 *db = nil;

- (void)openDataBase {
    
    int result = sqlite3_open(self.dataBasePath.UTF8String, &db);
    if (result == SQLITE_OK) {
        
        NSLog(@"打开成功");
        NSLog(@"%@", self.dataBasePath);
    } else {
        NSLog(@"打开失败");
    }
}

#pragma mark - collection
- (void)createTable {
    
    NSString *sqlString = @"create table if not exists collection(id integer primary key autoincrement, title text not null, category text not null, link text not null)";
    int result = sqlite3_exec(db, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建collection表格成功");
    } else {
        NSLog(@"创建collection表格失败");
    }
}

- (void)insertIntoDataBase:(JEHCollectionModel *)collectionModel {
    
    NSString *sqlString = @"insert into collection(title,category,link) values (?,?,?)";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, collectionModel.title.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 2, collectionModel.category.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 3, collectionModel.link.UTF8String, -1, NULL);
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"插入collection成功");
        } else {
            NSLog(@"插入collection失败");
        }
    } else {
        NSLog(@"插入collection语句错误");
    }
    sqlite3_finalize(stmt);
}

- (NSArray *)selectAllFromDataBase {
    
    NSString *sqlString = @"select * from collection";
    sqlite3_stmt *stmt = NULL;
    NSMutableArray *mutableArray = [NSMutableArray arrayWithCapacity:0];
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            JEHCollectionModel *collectionModel = [[JEHCollectionModel alloc] init];
            NSString *title = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            NSString *categary = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            NSString *link = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];
            collectionModel.title = title;
            collectionModel.category = categary;
            collectionModel.link = link;
            [mutableArray addObject:collectionModel];
            [collectionModel release];
        }
    } else {
        NSLog(@"查询collection全部语句错误");
    }
    sqlite3_finalize(stmt);
    return mutableArray;
}

- (JEHCollectionModel *)selectWithTitle:(NSString *)title {
    
    NSString *sqlString = @"select title from collection where title = ?";
    sqlite3_stmt *stmt = NULL;
    JEHCollectionModel *collectionModel = [[JEHCollectionModel alloc] init];
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        
        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            NSString *selectTitle = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            collectionModel.title = selectTitle;
        }
    } else {
        NSLog(@"根据title查询collection语句错误");
    }
    sqlite3_finalize(stmt);
    return collectionModel;
}


- (void)deleteFromDataBase:(NSString *)title {
    NSString *sqlString = @"delete from collection where title = ?";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"删除collection成功");
        }
    } else {
        NSLog(@"删除collection语句错误");
    }
    sqlite3_finalize(stmt);
}

#pragma mark - titleArray
- (void)createTitleArrayTable {
    
    NSString *sqlString = @"create table if not exists titleArray(id integer primary key autoincrement, name text not null, url text not null)";
    int result = sqlite3_exec(db, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建title表格成功");
    } else {
        NSLog(@"创建title表格失败");
    }
}

- (void)insertTitleArrayIntoDataBase:(NSString *)name withUrl:(NSString *)url {
    
    NSString *sqlString = @"insert into titleArray(name,url) values (?,?)";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, name.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 2, url.UTF8String, -1, NULL);
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"插入title成功");
        } else {
            NSLog(@"插入title失败");
        }
    } else {
        NSLog(@"插入title语句错误");
    }
    sqlite3_finalize(stmt);
}

- (void)selectTitleArrayAllFromDataBase {
    
    self.nameArray = [NSMutableArray arrayWithCapacity:0];
    self.urlArray = [NSMutableArray arrayWithCapacity:0];
    NSString *sqlString = @"select * from titleArray";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            NSString *name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            NSString *url = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            
            [self.nameArray addObject:name];
            [self.urlArray addObject:url];
        }
    } else {
        NSLog(@"查询title全部语句错误");
    }
    sqlite3_finalize(stmt);
}

- (void)deleteTitleArrayFromDataBase:(NSString *)name {
    
    NSString *sqlString = @"delete from titleArray where name = ?";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, name.UTF8String, -1, NULL);
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"删除title成功");
        } else {
            NSLog(@"删除title失败");
        }
    } else {
        NSLog(@"删除title语句错误");
    }
    sqlite3_finalize(stmt);
}

- (BOOL)selectWithName:(NSString *)name {
    
    NSString *sqlString = @"select name from titleArray where name = ?";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare_v2(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        
        sqlite3_bind_text(stmt, 1, name.UTF8String, -1, NULL);
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            sqlite3_finalize(stmt);
            return YES;
        }
    } else {
        NSLog(@"根据title查询语句错误");
    }
    sqlite3_finalize(stmt);
    return NO;
}

#pragma mark - titleArray
- (void)createUserLoginTable {
    
    NSString *sqlString = @"create table if not exists userLogin(id integer primary key autoincrement, name text not null, url text not null)";
    int result = sqlite3_exec(db, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建title表格成功");
    } else {
        NSLog(@"创建title表格失败");
    }
}

- (void)insertUserLoginIntoDataBase:(NSString *)name withUrl:(NSString *)url {
    
    NSString *sqlString = @"insert into userLogin(name,url) values (?,?)";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, name.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 2, url.UTF8String, -1, NULL);
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"插入title成功");
        } else {
            NSLog(@"插入title失败");
        }
    } else {
        NSLog(@"插入title语句错误");
    }
    sqlite3_finalize(stmt);
}

- (void)selectUserLoginAllFromDataBase {
    
    self.nameArray = [NSMutableArray arrayWithCapacity:0];
    self.urlArray = [NSMutableArray arrayWithCapacity:0];
    NSString *sqlString = @"select * from userLogin";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            NSString *name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            NSString *url = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            
            [self.nameArray addObject:name];
            [self.urlArray addObject:url];
        }
    } else {
        NSLog(@"查询title全部语句错误");
    }
    sqlite3_finalize(stmt);
}

- (void)deleteUserLoginFromDataBase:(NSString *)name {
    
    NSString *sqlString = @"delete from userLogin where name = ?";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, name.UTF8String, -1, NULL);
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"删除title成功");
        } else {
            NSLog(@"删除title失败");
        }
    } else {
        NSLog(@"删除title语句错误");
    }
    sqlite3_finalize(stmt);
}

- (void)closeDataBase {
    
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"关闭成功");
    } else {
        NSLog(@"关闭失败 , %d", result);
    }
}

@end















